package com.etc;

import java.sql.*;

public class TestJDBCExercise {
        public static void testInsertDept(int deptno, String dname, String loc) {
            Connection connection = null;
            PreparedStatement pstmt = null;
            try {
                //加载驱动类
                Class.forName("oracle.jdbc.driver.OracleDriver");
                //获得connection
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                        "scott", "tiger");

                String sql = "insert into dept values(?,?,?)";
                pstmt = connection.prepareStatement(sql);
                pstmt.setInt(1, deptno);
                pstmt.setString(2, dname);
                pstmt.setString(3, loc);
                //返回数据库中受影响的行数
                int result = pstmt.executeUpdate();
                System.out.println(result > 0 ? "success" : "fail");

            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (pstmt != null) {
                    try {
                        pstmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        //更新记录，给定deptNp,更新dname,loc
        public static void testUpdatedept(int deptno, String dname, String loc) {
            Connection connection = null;
            Statement statement = null;
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                        "scott", "tiger");
                statement = connection.createStatement();
                String sql = "update dept set dname='" + dname + "',loc='" + loc + "' where deptno=" + deptno;
                int result = statement.executeUpdate(sql);
                System.out.println(result > 0 ? "success" : "fail");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }

        }

        public static void testDeletedept(int deptno) {
            Connection connection = null;
            PreparedStatement pstmt = null;
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                        "scott", "tiger");
                String sql = "delete from dept where deptno = ?";
                pstmt = connection.prepareStatement(sql);
                pstmt.setInt(1, deptno);
                int result = pstmt.executeUpdate();
                System.out.println(result > 0 ? "success" : "fail");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (pstmt != null) {
                    try {
                        pstmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        public static void testSelectDept() {
            String sql="select * from dept";
            ResultSet resultSet=DbUtil.executeQuery(sql);
            try {
                while(resultSet.next()){
                    System.out.println(resultSet.getInt(1)+"\t"+
                            resultSet.getString(1)+"\t"+
                            resultSet.getString("loc"));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DbUtil.close();
            }
        }

        public static void testSelectDeptByPk(int deptno)  {

                String sql = "select * from dept where deptno = ?";
                ResultSet resultSet = DbUtil.executeQuery(sql,deptno);
                try {
                    while (resultSet.next()) {
                        System.out.println(resultSet.getInt(1) + "\t" +
                                resultSet.getString(2) + "\t" +
                                resultSet.getString("loc"));
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    DbUtil.close();
                }
        }

        //emp表插入一条记录 数据值自定义
        //insert into emp values(8002,'SGX','CLERK',7902,to_date('1999/04/17','yyyy/mm/dd'),800.00,200.00,20);
        public static void testInsertEmp(int empno, String ename, String job, int mgr, String date, int sal, int comm, int deptno) {
            Connection connection = null;
            PreparedStatement pstmt = null;
            try {
                //加载驱动类
                Class.forName("oracle.jdbc.driver.OracleDriver");
                //获得connection
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                        "scott", "tiger");

                String sql = "insert into emp values(?,?,?,?,to_date(?,'yyyy/mm/dd'),?,?,?)";
                pstmt = connection.prepareStatement(sql);

                pstmt.setInt(1, empno);
                pstmt.setString(2, ename);
                pstmt.setString(3, job);
                pstmt.setInt(4, mgr);
                pstmt.setString(5, date);
                pstmt.setInt(6, sal);
                pstmt.setInt(7, comm);
                pstmt.setInt(8, deptno);
                //返回数据库中受影响的行数
                int result = pstmt.executeUpdate();
                System.out.println(result > 0 ? "success" : "fail");

            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (pstmt != null) {
                    try {
                        pstmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        public static void testUpdateEmp(int empno) {
            Connection connection = null;
            PreparedStatement pstat = null;
            PreparedStatement pstat1 = null;
            ResultSet resultSet = null;
            try {
                //加载驱动
                Class.forName("oracle.jdbc.driver.OracleDriver");
                //获取连接
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                        "scott", "tiger");
                String sql = "select * from emp where empno=?";
                String sql1 = "update emp set sal=? where empno=?";
                //获取pstar
                pstat = connection.prepareStatement(sql);
                pstat.setInt(1, empno);
                resultSet = pstat.executeQuery();
                if (resultSet.next()) {
                    int sal = resultSet.getInt("sal");
                    pstat1 = connection.prepareStatement(sql1);
                    pstat1.setInt(1, sal + 200);
                    pstat1.setInt(2, empno);
                    int result = pstat1.executeUpdate();
                    System.out.println(result > 0 ? "success" : "fail");

                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (pstat != null) {
                    try {
                        pstat.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (pstat1 != null) {
                    try {
                        pstat1.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        public static void testDeleteEmp(int empno) {
            Connection connection = null;
            PreparedStatement pstmt = null;
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE",
                        "scott", "tiger");
                String sql = "delete from emp where empno = ?";
                pstmt = connection.prepareStatement(sql);
                pstmt.setInt(1, empno);
                int result = pstmt.executeUpdate();
                System.out.println(result > 0 ? "success" : "fail");
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (pstmt != null) {
                    try {
                        pstmt.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        }


        public static void main(String[] args) {
//        testInsertDept(50,"IT","JL");
//        testUpdatedept(50,"IT2","JL2");
//        testDeletedept(50);
//        testSelectDept();
//        testSelectDeptByPk(10);
//        testInsertEmp(8001,"ZHANGSAN","CLERK",7902,"2000/01/01",800,200,20);
            testUpdateEmp(8001);
//        testDeleteEmp(8001);
        }
    }

